package com.peenongsaban.freelance.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.peenongsaban.freelance.bean.AptitudeBean;
import com.peenongsaban.freelance.bean.AptitudeTypeBean;
import com.peenongsaban.freelance.bean.PortfolioBean;
import com.peenongsaban.freelance.bean.UserBean;
import com.peenongsaban.freelance.connection.DatabaseConnectionPool;
import com.peenongsaban.freelance.dao.ListFreelanceDAO;
import com.peenongsaban.freelance.meta.AptitudeMeta;
import com.peenongsaban.freelance.meta.AptitudeTypeMeta;
import com.peenongsaban.freelance.meta.UserMeta;
import com.peenongsaban.freelance.model.ListFreelanceModel;
import com.peenongsaban.freelance.utils.ExceptionUtil;

public class ListFreelanceDAOImpl implements ListFreelanceDAO{

	@Override
	public Vector<AptitudeTypeBean> getListFreelanceByAptitudeTopHot() {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<AptitudeTypeBean> listAptitudeType = new Vector<AptitudeTypeBean>();
		PreparedStatement select_aptitude_type_statement = null;
		PreparedStatement select_aptitude_statement = null;
		PreparedStatement select_portfolio_statement = null;
		PreparedStatement select_portfolio_user_statement = null;
		try {
			String select_aptitude_type_sql = "";
			select_aptitude_type_statement = conn.prepareStatement(select_aptitude_type_sql);
			ResultSet rs_aptitude_type = select_aptitude_type_statement.executeQuery();
			
			while(rs_aptitude_type.next()){
				AptitudeTypeBean aptitudeType = new AptitudeTypeBean();
				aptitudeType.setAptitudeTypeID(rs_aptitude_type.getInt(AptitudeTypeMeta.COL_APTITUDE_TYPE_ID));
				aptitudeType.setAptitudeTypeName(rs_aptitude_type.getString(AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME));
				aptitudeType.setDescription(rs_aptitude_type.getString(AptitudeTypeMeta.COL_DESCRIPTION)); 
				aptitudeType.setCountSumFreelance(rs_aptitude_type.getInt(AptitudeTypeMeta.COL_SUM_COUNT_FREELANCE));
				
				Vector<AptitudeBean> listAptitude = new Vector<AptitudeBean>();
				String select_aptitude_sql = "";
				select_aptitude_statement = conn.prepareStatement(select_aptitude_sql);
				ResultSet rs_aptitude = select_aptitude_statement.executeQuery();
				
				while(rs_aptitude.next()){
					AptitudeBean aptitude = new AptitudeBean();
					aptitude.setAptitudeID(rs_aptitude.getInt(AptitudeMeta.COL_APTITUDE_ID));
					aptitude.setAptitudeName(rs_aptitude.getString(AptitudeMeta.COL_APTITUDE_NAME)); 
					
					Vector<PortfolioBean> listPortfolio = new Vector<PortfolioBean>();
					String select_portfolio_sql = "";
					select_portfolio_statement = conn.prepareStatement(select_portfolio_sql);
					ResultSet rs_portfolio = select_portfolio_statement.executeQuery();
					
					while(rs_portfolio.next()){
						PortfolioBean portfolio = new PortfolioBean();
						UserBean user = new UserBean();
						user.setAlias(rs_portfolio.getString(UserMeta.COL_ALIAS));
						user.setTrustLevel(rs_portfolio.getInt(UserMeta.COL_TRUST_LEVEL));
						user.setPicture(rs_portfolio.getString(UserMeta.COL_PICTURE));
						user.setCountEmploy(rs_portfolio.getInt(UserMeta.COL_COUNT_EMPLOY));
						user.setCountRequestJob(rs_portfolio.getInt(UserMeta.COL_COUNT_REQUEST_JOB));
						
						Vector<PortfolioBean> listPortfolio_user = new Vector<PortfolioBean>();
						String select_portfolio_user_sql = "";
						select_portfolio_user_statement = conn.prepareStatement(select_portfolio_user_sql);
						ResultSet rs_portfolio_user = select_portfolio_user_statement.executeQuery();
						while(rs_portfolio_user.next()){
							AptitudeBean aptitude_user = new AptitudeBean();
							aptitude_user.setAptitudeID(rs_portfolio_user.getInt(AptitudeMeta.COL_APTITUDE_ID)); 
							aptitude_user.setAptitudeName(rs_portfolio_user.getString(AptitudeMeta.COL_APTITUDE_NAME));
							
							PortfolioBean portfolio_user = new PortfolioBean();
							portfolio_user.setAptitude(aptitude_user);
							
							listPortfolio_user.add(portfolio_user);
						}
						
						user.setListPortfolio(listPortfolio_user); 
						
						portfolio.setUser(user);
						
						listPortfolio.add(portfolio);
					}
					
					aptitude.setListPortfolio(listPortfolio); 
					
					listAptitude.add(aptitude);
				}
				
				aptitudeType.setListAptitude(listAptitude); 
				
				listAptitudeType.add(aptitudeType);
				
			}
			
			return listAptitudeType;
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);		
		} finally {
			try {
				if(select_aptitude_type_statement != null)				
					select_aptitude_type_statement.close();
				if(select_aptitude_statement != null)	
					select_aptitude_statement.close();
				if(select_portfolio_statement != null)	
					select_portfolio_statement.close();
				if(select_portfolio_user_statement != null)	
					select_portfolio_user_statement.close();
				
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return null;
	}

	@Override
	public Vector<AptitudeTypeBean> getListFreelanceByAptitudeType(ListFreelanceModel model) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<AptitudeTypeBean> listAptitudeType = new Vector<AptitudeTypeBean>();
		PreparedStatement select_aptitude_type_statement = null;
		PreparedStatement select_aptitude_statement = null;
		PreparedStatement select_portfolio_statement = null;
		PreparedStatement select_portfolio_user_statement = null;
		try {
			String select_aptitude_type_sql = "";
			select_aptitude_type_statement = conn.prepareStatement(select_aptitude_type_sql);
			ResultSet rs_aptitude_type = select_aptitude_type_statement.executeQuery();
			
			while(rs_aptitude_type.next()){
				AptitudeTypeBean aptitudeType = new AptitudeTypeBean();
				aptitudeType.setAptitudeTypeID(rs_aptitude_type.getInt(AptitudeTypeMeta.COL_APTITUDE_TYPE_ID));
				aptitudeType.setAptitudeTypeName(rs_aptitude_type.getString(AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME));
				aptitudeType.setDescription(rs_aptitude_type.getString(AptitudeTypeMeta.COL_DESCRIPTION)); 
				aptitudeType.setCountSumFreelance(rs_aptitude_type.getInt(AptitudeTypeMeta.COL_SUM_COUNT_FREELANCE));
				
				Vector<AptitudeBean> listAptitude = new Vector<AptitudeBean>();
				String select_aptitude_sql = "";
				select_aptitude_statement = conn.prepareStatement(select_aptitude_sql);
				ResultSet rs_aptitude = select_aptitude_statement.executeQuery();
				
				while(rs_aptitude.next()){
					AptitudeBean aptitude = new AptitudeBean();
					aptitude.setAptitudeID(rs_aptitude.getInt(AptitudeMeta.COL_APTITUDE_ID));
					aptitude.setAptitudeName(rs_aptitude.getString(AptitudeMeta.COL_APTITUDE_NAME)); 
					
					Vector<PortfolioBean> listPortfolio = new Vector<PortfolioBean>();
					String select_portfolio_sql = "";
					select_portfolio_statement = conn.prepareStatement(select_portfolio_sql);
					ResultSet rs_portfolio = select_portfolio_statement.executeQuery();
					
					while(rs_portfolio.next()){
						PortfolioBean portfolio = new PortfolioBean();
						UserBean user = new UserBean();
						user.setAlias(rs_portfolio.getString(UserMeta.COL_ALIAS));
						user.setTrustLevel(rs_portfolio.getInt(UserMeta.COL_TRUST_LEVEL));
						user.setPicture(rs_portfolio.getString(UserMeta.COL_PICTURE));
						user.setCountEmploy(rs_portfolio.getInt(UserMeta.COL_COUNT_EMPLOY));
						user.setCountRequestJob(rs_portfolio.getInt(UserMeta.COL_COUNT_REQUEST_JOB));
						
						Vector<PortfolioBean> listPortfolio_user = new Vector<PortfolioBean>();
						String select_portfolio_user_sql = "";
						select_portfolio_user_statement = conn.prepareStatement(select_portfolio_user_sql);
						ResultSet rs_portfolio_user = select_portfolio_user_statement.executeQuery();
						while(rs_portfolio_user.next()){
							AptitudeBean aptitude_user = new AptitudeBean();
							aptitude_user.setAptitudeID(rs_portfolio_user.getInt(AptitudeMeta.COL_APTITUDE_ID)); 
							aptitude_user.setAptitudeName(rs_portfolio_user.getString(AptitudeMeta.COL_APTITUDE_NAME));
							
							PortfolioBean portfolio_user = new PortfolioBean();
							portfolio_user.setAptitude(aptitude_user);
							
							listPortfolio_user.add(portfolio_user);
						}
						
						user.setListPortfolio(listPortfolio_user); 
						
						portfolio.setUser(user);
						
						listPortfolio.add(portfolio);
					}
					
					aptitude.setListPortfolio(listPortfolio); 
					
					listAptitude.add(aptitude);
				}
				
				aptitudeType.setListAptitude(listAptitude); 
				
				listAptitudeType.add(aptitudeType);
				
			}
			
			return listAptitudeType;
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);		
		} finally {
			try {
				if(select_aptitude_type_statement != null)				
					select_aptitude_type_statement.close();
				if(select_aptitude_statement != null)	
					select_aptitude_statement.close();
				if(select_portfolio_statement != null)	
					select_portfolio_statement.close();
				if(select_portfolio_user_statement != null)	
					select_portfolio_user_statement.close();
				
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return null;
	}

}
